The final numbered post in this version of my “pillar” series of posts ends in the most contestable part of the design/implementation process.  Encapsulation. The concept of encapsulation is not contested (or even contestable by sane programmers in any field of the art of system creation. Every time you use a Windows API call you are participating in encapsulation. Every language that doesn’t look like:

0101000101001010101010100101010101010101010101010101010100000001110010101

is using some level of encapsulation to make life easier.  The problem isn’t encapsulation, it is who do we allow to perform the encapsulation.

It is at this point in the process that I have to make something clear.  As a writer in a non-religious instructional topic, you need to be reasonably open to other people’s ideas and concepts and ideas, and no where in database design does this get contentious like when talking about encapsulation. I defined encapsulated in this concepts in the following terms: “Changes to the structures cause only changes to usage where a table/column directly accessed it”

The obvious answer to any long term dba is that by establishing a solid layer of stored procedures to allow others to access the data is the easiest way to achieve these goals.  Just like the Windows API that you call to get a message box on a 64 bit server or this little mini laptop I am typing on right now, stored procedures give you an API the encapsulates all of the joins, inserts, etc away from the other layers of the application. Now the data programmer can adjust the API if desired, but can also remove the entire db and replace all the tables with new tables, if desired.  Rarely does this occur, but it is not uncommon to have multiple apps using the same database structures, and sometimes the needs differ (perhaps one app only needs one address for a person, the other can use all of them…).

So at this point, the common argument is “why go through all of this when I can let my ORM deal with the database?” and it is a not uncompelling argument. As a bonafide lazy person, I hate to do more work than necessary. The problem is, the future.  I learned a long time ago to honor future-me in as many ways as I can (well, eating right and exercising not withstanding) and future-me wants to hang out and admire all of the work current-me did and either relax or build something new for even later in the future me. I hate doing rework.  The motto of my website (drsql.org) “an ounce of design is worth a pound of rework” is a direct reflection of that.  Design now…enjoy later. Some of my favorite production moves were done on stored procedure access systems.  I had built and tested the heck out of the database, and was ready for nearly anything, the other layers not so much. I sat around and taunted them mostly, though I am not perfect, the problem is far easier to manage on the data layer.

It is at this point we pause to admit that the database layer is so much easier to do right than the other layers.  There are fewer moving parts, fewer things to go wrong, and honestly, the SQL Server Dev team gives us a platform that just freaking works. And the foundational principals that Codd came up with 30 years ago constantly amaze me at how well they just work. And look, the code we wrote 16 years ago will still run with minor modifications (that code can be enhanced with new features, but T-SQL strongly resembles the code we wrote all those years ago.

That having been said, to be honest, I said that the argument for ORM’s was not uncompelling, and with some reservations, I find it all too frequent that I have to give in to the ORM craze. The only concern I have is that the database is becoming less of a platform that we code to, and more of a storage platform only.  While initially this sounds like I am trying to protect my job, it really isn’t that at all. My problem is that there are a few consumers of the data that ORM’s won’t do an adequate job for unless the database is built for the database server, and not for the ORM.

• Reporters – Best case, we have a BI solution such that users don’t have to access the OLTP database. But someone has to write queries to get the data out
• Future ORMs – No one likes to get nailed down to one programming methodology other than SQL programmers. Most of the products that were around when we were doing 4.21 programming are gone now or morphed into something bizarrely different (like VB). What is good for ORM A might not be good for ORM B.  So now we need to do data conversion? Too costly.
• Importers/Outside sources – Poorly normalized databases make it harder to import data from outside sources without using the API, and most shops are going to do bulk loading of data at some point.

Note I said poorly normalized databases. Is this guaranteed to be the case? No, but any database I have seen designed for the needs of the screen/programmer always looks vastly “different” than a database that was designed for the data platform.

So, unlike the other pillars, this one turned into an op-ed piece, rather than straight facts. Sorry, but it is just that type of problem. There are good points on both sides of the equation, but the real problem is that where data platform pays off is over time. The average turnover for a programmer (any type) is said to be around 2 years. Who cares about the future when you plan to be somewhere else?  Personally I have been with my current employer for 12 years, and hope to go another 20+. And I see/feel the mistakes of the past, while many of my coworkers are somewhere else, grousing about how horrible someone else’s code is…

Final comments on this topic. Any solution that meets the criteria “Changes to the structures cause only changes to usage where a table/column directly accessed it”, or even one that recognizes the problem is a major step in the right direction. I always joke with people in my sessions that if you  are attending my session, you probably care about doing things right. Same with this concept.  If you are paying attention to the future, at the very least you probably won’t leave future-you in a bind, even if future-you is a totally different person.